package com.scalar.chiptrack.sales.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.sales.TopCustomers;
import com.scalar.chiptrack.sales.TopCustomer;
import com.scalar.chiptrack.operations.process.FabStageGroup;
import com.scalar.chiptrack.operations.process.dao.FabStage;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TopCustomersDAO
{
    public static ArrayList loadExistingCustomers( DBTransaction dbTransaction, int rank , int customerType ) throws ScalarException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         ArrayList customersList = null;

         try
         {
             if ( dbTransaction == null )
             {
                 conn = DataSource.getConnection();
             }
             else
             {
                 conn = dbTransaction.getConnection();
             }

             pstmt = conn.prepareStatement ("SELECT distinct C.CUSTOMER_ID, C_NAME FROM CUSTOMER C, CUSTOMER_TYPE_RELATIONSHIP CTR, TOP_20_CUSTOMER_DETAILS_TABLE TOP20 WHERE  top20.C_TYPE_ID= ? AND CTR.CUSTOMER_ID=C.CUSTOMER_ID AND C.C_DISABLED=1 and CTR.CUSTOMER_ID=top20.CUSTOMER_ID and top20.order_no=? order by C_name");
             pstmt.setInt( 1 , customerType );
             pstmt.setInt( 2 , rank );

             rs = pstmt.executeQuery();

             customersList = new ArrayList();

             while ( rs.next() )
             {
                 customersList.add( new org.apache.struts.util.LabelValueBean ( rs.getString("c_name") , rs.getInt( "Customer_id" )+"" ) );
             }
         }
         catch(SQLException sql)
         {
             sql.printStackTrace();
             throw new ScalarException(" While get the Customers from customers Data ", sql);
         }
         finally
         {
             if(rs != null)
             {
                 try{ rs.close();}catch(Exception e){}
             }
             if(pstmt != null)
             {
                 try{ pstmt.close(); }catch(Exception e){}
             }
             if(conn != null)
             {
                 try{ conn.close(); }catch(Exception e){}
             }
         }

         return customersList;
    }
    public static ArrayList loadCustomersRanks( int customerType  ) throws ScalarException
      {
          Connection conn             = null;
          PreparedStatement pstmt     = null;
          ResultSet rs                = null;
          ArrayList ranksList     = null;
          TopCustomers topCustomers = null;

          try
          {
              conn = DataSource.getConnection();

              pstmt = conn.prepareStatement ("select distinct  ORDER_NO, CUSTOMER_GROUP_NAME from TOP_20_CUSTOMER_DETAILS_TABLE where c_type_id= ? ORDER BY ORDER_NO");
              pstmt.setInt( 1 , customerType);
              rs = pstmt.executeQuery();

              ranksList = new ArrayList();

              while ( rs.next() )
              {
                  topCustomers = new TopCustomers();
                  topCustomers.setRank( rs.getInt("ORDER_NO") );
                  topCustomers.setCustomersAlisName ( rs.getString("CUSTOMER_GROUP_NAME") );
                  topCustomers.setCTypeId(customerType);
                  ranksList.add( topCustomers );
              }
              
          }
          catch(SQLException sql)
          {
              sql.printStackTrace();
              throw new ScalarException(" While get the RANKS FROM  db failed", sql);
          }
          finally
          {
              if(rs != null)
              {
                  try{ rs.close();}catch(Exception e){}
              }
              if(pstmt != null)
              {
                  try{ pstmt.close(); }catch(Exception e){}
              }
              if(conn != null)
              {
                  try{ conn.close(); }catch(Exception e){}
              }
          }

          return ranksList;
     }
    public static ArrayList loadAvailableCustomers( DBTransaction dbTransaction,  int customerType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList availableStagesList = null;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ("SELECT C_name, c.customer_ID FROM customer c, CUSTOMER_TYPE_RELATIONSHIP CTR where c.customer_id= ctr.customer_id  and C_DISABLED=1 and CTR.C_TYPE_ID=? and c.customer_id NOT IN ( SELECT customer_id FROM TOP_20_CUSTOMER_DETAILS_TABLE TOP20 where top20.c_type_id= ?) order by C_name ");
            pstmt.setInt( 1 , customerType );
            pstmt.setInt( 2 , customerType );
            rs = pstmt.executeQuery();

            availableStagesList = new ArrayList();

            while ( rs.next() )
            {
                availableStagesList.add( new org.apache.struts.util.LabelValueBean ( rs.getString("c_name") , rs.getInt( "customer_id" )+"" ) );
            }
        }
        catch(SQLException sql)
        {
            sql.printStackTrace();
            throw new ScalarException(" While get the error in customer Detials ", sql);
        }
        finally
        {
            if(rs != null)
            {
                try{ rs.close();}catch(Exception e){}
            }
            if(pstmt != null)
            {
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null)
            {
                try{ conn.close(); }catch(Exception e){}
            }
        }
        return availableStagesList;
   }

    public static boolean saveTopCustomers(DBTransaction dbTransaction , TopCustomer topCustomer ) throws ScalarException
     {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rs            = null;
        boolean status          = false;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            int selectedCustomersId = 0;
            ArrayList topCustomersList = topCustomer.getTopCustomers();
            int rank =0;
            int cTypeId = 1;
            String[] selectedCustomers = null;
            String customerName  = null;
            cTypeId =topCustomer.getCTypeId();

            pstmt = conn.prepareStatement("DELETE from TOP_20_CUSTOMER_DETAILS_TABLE where c_type_id=?");
            pstmt.setInt(1, topCustomer.getCTypeId());
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;
            for (int i = 0; i < topCustomersList.size(); i++)
            {
                int groupId;
                boolean remove;
                TopCustomers topCustomers = ( TopCustomers ) topCustomersList.get(i);
                selectedCustomers = topCustomers.getSelectedCustomers();
                customerName = topCustomers.getCustomersAlisName();
                rank   = topCustomers.getRank();
                int selectedCustomerId = 0;
                    if ( selectedCustomers != null && selectedCustomers.length > 0 )
                    {
                        pstmt = conn.prepareStatement("INSERT INTO TOP_20_CUSTOMER_DETAILS_TABLE (customer_id, C_TYPE_ID, CUSTOMER_GROUP_NAME, ORDER_NO, TIMESTAMP  ) VALUES (?,?,?, ?, CURRENT_TIMESTAMP) ");
                        for (int j = 0; j < selectedCustomers.length; j++)
                        {
                            try
                            {
                                selectedCustomerId = Integer.parseInt(selectedCustomers[j] ) ;
                            }
                            catch ( NumberFormatException nfe)
                            {
                                selectedCustomerId = 0;
                            }
                            if ( selectedCustomerId > 0 && rank > 0 )
                            {
                                pstmt.setInt ( 1 , selectedCustomerId);
                                pstmt.setInt ( 2 , cTypeId );
                                pstmt.setString(3, customerName);
                                pstmt.setInt(4, rank);
                                pstmt.addBatch();

                            }
                        }
                         pstmt.executeBatch();
                    }
                }
            status = true;
        }
        catch( SQLException sql )
        {
            sql.printStackTrace();
            throw new ScalarException(" Exception while Saving Top 20 Customers Failed", sql);
        }

        finally
        {
            try
                {
                    if ( dbTransaction == null )
                    {
                        if ( conn != null )
                        {
                            if ( status == true )
                            {
                                conn.commit();
                            }
                            else
                            {
                                conn.rollback();
                            }
                        }
                    }
                }
                catch ( SQLException ignore ) {}
            if(rs != null){
                try{ rs.close(); }catch(Exception e){}
            }
            if(pstmt != null){
                try{ pstmt.close(); }catch(Exception e){}
            }
            if(conn != null){
                try{ conn.close(); }catch(Exception e){}
            }
        }
        return status;
    }

}
